• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Get SQL Server Agent Status from client M\C without having SQL Server Client objects installed loca

Get SQL Server Agent Status from client M\C without having SQL Server Client objects installed loca

Get SQL Server Agent Status from client M\C without having SQL Server Client objects installed locally, just using a stored procedure.

API Declarations



Private Rs As New ADODB.Recordset





Rate Get SQL Server Agent Status from client M\C without having SQL Server Client objects installed loca

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

CREATE PROCEDURE [dbo].[spAgentStatus]

@user		varchar(30),	-- VALID SQL Server Login
@pwd		varchar(30)	-- VALID Login Password

AS

-- Returned information
DECLARE @source 	varchar (255)
DECLARE @description 	varchar (255)
DECLARE @status_msg	varchar(100) 
DECLARE @Status		int 

-- Internal variables
DECLARE @object int
DECLARE @hr int

-- Create SQLDMO Object
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT 
   SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
   RETURN
END

-- Call Method to connect to the server 
EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @@ServerName, @user, @pwd
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT 
   SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
   RETURN
END

-- Get property value for the SQL Server Agent current status
DECLARE @property varchar(255)
EXEC @hr = sp_OAGetProperty @object, 'JobServer.Status', @property OUT
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT 
   SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
   RETURN
END

--Get status information to return
SET @status_msg = CASE @property WHEN 1 THEN 'SQL Server Agent is running'
                             WHEN 3 THEN 'SQL Server Agent is stopped' 
                             WHEN 2 THEN 'SQL Server Agent is paused'
                             WHEN 6 THEN 'SQL Server Agent is in transition from paused to running'
                             WHEN 7 THEN 'SQL Server Agent is in transition from running to paused'
                             WHEN 4 THEN 'SQL Server Agent is in transition from stopped to running'
                             WHEN 5 THEN 'SQL Server Agent is in transition from running to stopped'
			     ELSE 'Unable to determine service execution state.' 	
               END
SET @status = @property

-- clean up objects
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @source OUT, @description OUT 
   SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
   RETURN
END

-- if no errors return status information
SELECT Source=@source, Description=@description, status_msg = @status_msg, status = @status
GO

/* End of Stored Procedure */


    With Rs
        .ActiveConnection = gCon_cn
        .Open "kt_sysAgentStatus '" & App.title & "','" & gStr_Password & "'"
        If IsNull(Rs("Source").value) Then
            If Not IsNull(Rs("Status").value) Then If Rs("Status").value = 1 Then mBol_AgentRunning = True
            If Not IsNull(Rs("Status_msg").value) Then buffer = Rs("Status_msg").value
        Else
            If Not IsNull(Rs("Source").value) Then buffer = "AGENT STATUS ERROR : " & Rs("Status").value & "  - "
            If Not IsNull(Rs("Descripton").value) Then buffer = buffer & Rs("Description").value
        End If